/**
 * 项目名：五位一体化工安全信息平台
 * 日期：    2019-12-08 15:07:44
 * Copyright (c) 2015- joysuch-版权所有
 */

package com.joysuch.wwyt.bp.repository;

import java.util.Date;
import java.util.List;
import java.util.Set;

import javax.persistence.Tuple;

import com.joysuch.wwyt.bp.entity.contractor.BpContractorStaff;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.transaction.annotation.Transactional;


public interface BpContractorStaffDao extends JpaRepository<BpContractorStaff, Long>,
        JpaSpecificationExecutor<BpContractorStaff>, QuerydslPredicateExecutor<BpContractorStaff> {

    @Query(value = "select cs.id as id,cs.code,cs.name as staffName,c.company_name as contractorName,c.contractor_type as contractorType ," +
            " cs.ref_url as refUrl ,cs.alarm_template_id as alarmTemplateId,cs.alarm_template_name as alarmTemplateName "
            + ",cs.locate_sync as locateSync, cs.sn as sn , cs.TELEPHONE as telPhone "
            + "from wwyt_bp_contractor_staff cs left join wwyt_bp_contractor c on cs.contractor_id =c.id "
            + "where cs.delete_flag='0' and c.delete_flag='0' and cs.state=1 and c.state=1 and if(:name is null,1=1,"
            + "cs.name like %:name% or cs.id_card_no like %:name%) and if(:contractorType is null,1=1,c.contractor_type=:contractorType) "
            + "and if(:contractorId is null,1=1,c.id=:contractorId) and if (:sn is null, 1=1, cs.sn like %:sn%) "
            + "and if(:locateSync is null,1=1, cs.locate_sync = :locateSync) order by cs.id desc ", nativeQuery = true)
    List<Tuple> page(String name, String contractorType, String contractorId, String sn , Boolean locateSync);

    @Query(value = "update BpContractorStaff b set b.state= 0 where b.contractorId=:contractorId")
    @Modifying
    void nullify(Long contractorId);

    @Query(value = "select cs.id as id,cs.code as code,cs.name as name,c.company_name as contractorName "
            + "from wwyt_bp_contractor_staff cs left join wwyt_bp_contractor c on cs.contractor_id=c.id "
            + "where cs.delete_flag='0' and cs.state=1 and c.delete_flag='0' and c.state=1 "
            + "and c.contractor_type='合格承包商' "
            + "and if(:name is null,1=1,cs.name like %:name% or cs.code like %:name%) "
            + "and if(:contractorId is null,1=1,cs.contractor_id=:contractorId) order by cs.id desc ", nativeQuery = true)
    List<Tuple> userModelPage(String name, Long contractorId);

    @Query(value = "select cs.id as id,cs.code as code,cs.name as name,c.company_name as contractorName "
            + "from wwyt_bp_contractor_staff cs left join wwyt_bp_contractor c on cs.contractor_id=c.id "
            + "where cs.delete_flag='0' and cs.state=1 and c.delete_flag='0' and c.state=1 "
            + "and c.contractor_type='合格承包商' "
            + "and if(:name is null,1=1,cs.name like %:name% or cs.code like %:name%) "
            + "and if(:contractorId is null,1=1,cs.contractor_id=:contractorId) "
            + "and if(:tenantId is null,1=1,cs.TENENT_ID=:tenantId) "
            + "and if(:orgCode is null,1=1,cs.ORG_CODE like :orgCode%) "
            + "order by cs.id desc "
            , nativeQuery = true)
    List<Tuple> userModelWfPage(String name, Long contractorId,Long tenantId, String orgCode);

    @Query(value = "select cs.id as userId,cs.code as code,cs.name as name,c.company_name as companyName "
            + "from wwyt_bp_contractor_staff cs left join wwyt_bp_contractor c on cs.contractor_id=c.id "
            + "where cs.delete_flag='0' and cs.state=1 and c.delete_flag='0' and c.state=1 "
            + "and c.contractor_type='合格承包商' " + "and if(:enterPrise is null,1=1,c.company_name=:enterPrise) "
            + "and if(:name is null,1=1,cs.name like %:name% or cs.code like %:name%) order by cs.id desc", nativeQuery = true)
    List<Tuple> getEduContractorUserCert(String enterPrise, String name);

    @Query(value = "select * from (select cs.id as userId,cs.code as code,cs.name as name,c.company_name as companyName "
            + "from wwyt_bp_contractor_staff cs left join wwyt_bp_contractor c on cs.contractor_id=c.id "
            + "where cs.delete_flag='0' and cs.state=1 and c.delete_flag='0' and c.state=1 "
            + "and c.contractor_type='合格承包商' and cs.tenent_id=:tenentId and cs.org_code like :orgCode% "
            + "and c.tenent_id=:tenentId and c.org_code like :orgCode% order by cs.id desc) as  p1" + "union"
            + "select * from (select cs.id as userId,cs.code as code,cs.name as name,c.company_name as companyName "
            + "from wwyt_bp_contractor_staff cs left join  wwyt_edu_certificate_user ecu on ecu.user_id=cs.id "
            + "inner join wwyt_bp_contractor c on cs.contractor_id=c.id "
            + "where cs.delete_flag='0' and cs.state=1 and c.delete_flag='0' and c.state=1 "
            + "and ecu.delete_flag='0' and c.contractor_type='合格承包商' "
            + "and cs.tenent_id=:tenentId and cs.org_code like :orgCode% and c.tenent_id=:tenentId and c.org_code like :orgCode% "
            + "and ecu.tenent_id=:tenentId and ecu.org_code like :orgCode% order by  cs.id desc)", nativeQuery = true)
    List<Tuple> getEduContractorUserCertNoQuery(Long tenentId, String orgCode);

    @Query(value = "select cs.id as userId,cs.code as code,cs.name as name,c.company_name as companyName "
            + "from wwyt_bp_contractor_staff cs left join wwyt_bp_contractor c on cs.contractor_id=c.id "
            + "inner join wwyt_edu_certificate_user ecu on ecu.user_id=cs.id "
            + "where cs.delete_flag='0' and cs.state=1 and c.delete_flag='0' and c.state=1 "
            + "and ecu.user_contractor_flag=1 " + "and if(:enterPrise is null,1=1,c.company_name=:enterPrise) "
            + "and if(:name is null,1=1,cs.name like %:name% or cs.code like %:name%) "
            + "and ecu.cert_id=:certId and c.contractor_type='合格承包商' ", nativeQuery = true)
    List<Tuple> getEduContractorUserCertWithCertId(String enterPrise, String name, Long certId);

    @Query("select m.name from BpContractorStaff m where m.id=:userId")
    String findNameById(Long userId);

    @Query(value = "select id from BpContractorStaff where name like %?1%")
    List<Long> getUserIdByNameContains(String name);

    @Query(value = "select * from wwyt_bp_contractor_staff where code=?1 and org_code like ?2% and state=1 limit 1", nativeQuery = true)
    BpContractorStaff findByCodeAndCompanyCode(String code, String companyCode);

    @Query(value = " SELECT id FROM wwyt_bp_contractor_staff WHERE name like %:userName% ", nativeQuery = true)
    List<Long> getStaffIdByNameContains(String userName);

    @Query(value = "select * from wwyt_bp_contractor_staff where (sn is not null and employee_Id is null)", nativeQuery = true)
    List<BpContractorStaff> findBySnNotNullAndEmployeeIdNull();

    @Query(value = "select * from wwyt_bp_contractor_staff where (sn is null and employee_Id is not null)", nativeQuery = true)
    List<BpContractorStaff> findBySnNullAndEmployeeIdNotNull();

    @Query(value = "select * from wwyt_bp_contractor_staff where sn =?1 and state=1", nativeQuery = true)
    BpContractorStaff findOneBySn(String sn);

    BpContractorStaff findByCode(String code);

    @Query(value = " select * from wwyt_bp_contractor_staff where CODE = ?1 and state=1 and DELETE_FLAG = '0' limit 1 ", nativeQuery = true)
    BpContractorStaff findByCodeANDAndState(String code);

    @Query(value = "select cs.id as userId,cs.code as code,cs.name as name,c.company_name as companyName "
            + "from wwyt_bp_contractor_staff cs left join wwyt_bp_contractor c on cs.contractor_id = c.id "
            + "inner join wwyt_edu_certificate_user ecu on ecu.user_id=cs.id "
            + "where cs.delete_flag='0' and cs.state=1 and c.delete_flag='0' and c.state=1 "
            + "and ecu.user_contractor_flag=1 " + "and if(:enterPrise is null,1=1,c.company_name=:enterPrise) "
            + "and if(:name is null,1=1,cs.name like %:name% or cs.code like %:name%) "
            + "and c.contractor_type='合格承包商' and ecu.cert_id in (:certIdList)  "
            + "and (ecu.out_of_date_time is null or ecu.out_of_date_time >= :date) order by cs.id desc", nativeQuery = true)
    List<Tuple> getEduContractorUserCertWithCertIds(String enterPrise, String name,
                                                    @Param("certIdList") List<Long> certIdList, Date date);

    BpContractorStaff findFirstByCardNumberAndState(String cardNumber, int state);

    @Query(value = "SELECT * FROM wwyt_bp_contractor_staff WHERE contractor_id =?1 AND state = 1 AND delete_flag = '0'", nativeQuery = true)
    List<BpContractorStaff> findByContractorId(Long id);

    List<BpContractorStaff> findByCardNumberAndState(String cardNumber, int i);

    List<BpContractorStaff> findBySnAndState(String sn, int i);

    @Query(value = "select * from wwyt_bp_contractor_staff where name =?1  and code =?2 and state = 1 and delete_flag = '0'", nativeQuery = true)
    BpContractorStaff findByNameAndCode(String userName, String userCode);

    @Query(value = "select * from wwyt_bp_contractor_staff where (name like %:nameOrCode%  or code like %:nameOrCode%) and delete_flag = '0'", nativeQuery = true)
    List<Long> findIdsByNameOrCode(@Param("nameOrCode") String nameOrCode);

    @Query(value = "SELECT s.* FROM wwyt_bp_contractor_staff s INNER JOIN wwyt_base_image_repository r ON s.cap_id = r.id WHERE s.sn IS NOT NULL AND s.unique_id IS NOT NULL AND s.cap_id IS NOT NULL AND s.locate_sync = 1 AND s.delete_flag ='0' AND s.state =1 AND r.unique_id IS NOT NULL AND r.locate_sync = 1", nativeQuery = true)
    List<BpContractorStaff> findBySnAndLocateSync();

    @Transactional
    @Modifying
    @Query(value = "UPDATE wwyt_bp_contractor_staff SET state = ?2 WHERE id IN ?1", nativeQuery = true)
    void setState(List<Long> ids, Integer state);

    @Transactional
    @Modifying
    @Query(value = "UPDATE wwyt_bp_contractor_staff SET locate_sync = ?2 WHERE id IN ?1", nativeQuery = true)
    void editLocateSyncByIds(List<Long> unSyncIds, Integer i);

    @Query(value = "select count(*) from wwyt_bp_contractor_staff where cap_id=?1  and state = 1 and delete_flag = '0'", nativeQuery = true)
    int countByCapId(Long typeId);

    @Query(value = "SELECT id FROM wwyt_bp_contractor_staff WHERE contractor_id in ?1 and delete_flag = '0' ", nativeQuery = true)
    Set<Long> findStaffIdsByContractorIds(Set<Long> contractorIds);

    @Query(value = " SELECT " +
            "cs.id AS id " +
            "FROM " +
            "wwyt_bp_contractor_staff cs " +
            "LEFT JOIN wwyt_bp_contractor c ON cs.contractor_id = c.id " +
            "WHERE " +
            "cs.delete_flag = '0' " +
            "AND cs.state = 1 " +
            "AND c.delete_flag = '0' " +
            "AND c.state = 1 " +
            "AND c.contractor_type = '合格承包商' and cs.code = ?1 limit 1", nativeQuery = true)
    Long findStaffByCode(String code);
}
